USE NutritionTracker
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE ID = object_id(N'NavigationMenu_Read_List') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE NavigationMenu_Read_List
GO
CREATE PROC NavigationMenu_Read_List
(
@NavMenuID TINYINT = NULL
)
AS
BEGIN
	WITH RecursiveNavMenu
	(
		NavMenuID,
		ParentNavMenuID,
		NavMenuName,
		NavURL,
		SortOrder,
		Level
	)
	AS
	(
		SELECT		NavigationMenu.NavMenuID,
				NavigationMenu.ParentNavMenuID,
				NavigationMenu.NavMenuName,
				NavigationMenu.NavURL,
				NavigationMenu.SortOrder,
				0 AS Level
		FROM		NavigationMenu
		WHERE		1 = CASE
							WHEN @NavMenuID IS NULL THEN 
								CASE
									WHEN NavigationMenu.ParentNavMenuID IS NULL THEN 1
									ELSE 0
								END
							WHEN NavigationMenu.NavMenuID = @NavMenuID THEN 1
							ELSE 0
						END
		UNION ALL
		SELECT		NavigationMenu.NavMenuID,
				NavigationMenu.ParentNavMenuID,
				NavigationMenu.NavMenuName,
				NavigationMenu.NavURL,
				NavigationMenu.SortOrder,
				Level + 1
		FROM		NavigationMenu
		INNER JOIN	RecursiveNavMenu
			ON		NavigationMenu.	ParentNavMenuID = RecursiveNavMenu.NavMenuID
	)

	SELECT		NavMenuID,
			ParentNavMenuID,
			NavMenuName,
			NavURL,
			SortOrder,
			Level
	FROM		RecursiveNavMenu

END
GO
GRANT EXECUTE ON NavigationMenu_Read_List TO NUTRKR_WEB
GO